import sqlite3
from datetime import datetime, timedelta
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
# 设置matplotlib使用非交互式后端，避免在非主线程中启动GUI
import matplotlib
matplotlib.use('Agg')
# 设置matplotlib支持中文显示
plt.rcParams['font.sans-serif'] = ['SimHei', 'Arial Unicode MS', 'DejaVu Sans']
plt.rcParams['axes.unicode_minus'] = False
def get_watergrass(timepast=8):
    conn = sqlite3.connect(r'D:\myGitee\watergrassyolo\data\yolo.db')
    cursor = conn.cursor()
    cursor.execute(
        '''SELECT timestamp FROM watergrass ORDER BY timestamp DESC''')
    timestamp = cursor.fetchone()[0]
    timestamp = int(timestamp) - timepast * 3600
    # 修改为按时间升序排列，确保最新时间点在结果末尾
    cursor.execute(
        f'''SELECT wglarea,imgarea,timestamp,guessarea FROM watergrass WHERE timestamp >=? ORDER BY timestamp ASC ''',
        (timestamp, ))
    results = cursor.fetchall()
    conn.close()
    watergrass_data = []
    for wgarea, imgarea, timestamp, guessarea in results:
        date_str = datetime.fromtimestamp(timestamp).strftime(
            '%Y-%m-%d %H:%M:%S')
        watergrass_data.append([
            round(wgarea / imgarea * 100, 2), date_str,
            round(guessarea / imgarea * 100, 2) if guessarea else 0
        ])
    # print(watergrass_data)
    return watergrass_data
def save_plt(timepast=4,locator='hour'):
    '''
    生成曲线图
    '''
    print(f"?{timepast} 小时 曲线图生成中...")
    # 获取数据
    watergrass_data = get_watergrass(timepast)

    if not watergrass_data:
        return

    # 分离数据
    timestamps = []
    wglarea_ratios = []
    guessarea_ratios = []

    for item in watergrass_data:
        wglarea_ratio, date_str, guessarea_ratio = item
        # 转换日期字符串为datetime对象
        timestamp = datetime.strptime(date_str, '%Y-%m-%d %H:%M:%S')
        timestamps.append(timestamp)
        wglarea_ratios.append(wglarea_ratio)
        guessarea_ratios.append(guessarea_ratio)

    # 创建图表
    plt.figure(figsize=(12, 6))
    plt.plot(timestamps, wglarea_ratios, label='实际含量', marker='o')
    plt.plot(timestamps, guessarea_ratios, label='推测含量', marker='s')

    # 设置图表属性
    plt.xlabel('时间')
    plt.ylabel('含量百分比 (%)')
    plt.title(f'一站水草含量 (最近 {timepast} 小时)')
    plt.legend()
    plt.grid(True)

    # 格式化x轴时间显示
    plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%m-%d %H:%M'))
    if locator == 'hour':
        plt.gca().xaxis.set_major_locator(mdates.HourLocator(interval=1))
    elif locator =='minute':
        plt.gca().xaxis.set_major_locator(mdates.MinuteLocator(interval=5))
    plt.gcf().autofmt_xdate()
    
    # 确保最新的时间点在x轴上完整显示
    if timestamps:
        # 设置x轴范围稍微超过数据范围，确保最新时间点完整显示
        plt.xlim(timestamps[0], timestamps[-1] + timedelta(minutes=5))

    # 保存图表
    plt.savefig(f'D:/myGitee/control-net/data/onecloud/temp_wg{timepast}.jpg',
                dpi=100,
                bbox_inches='tight')
    plt.close()
    print(f"√{timepast} 小时 完成")
if __name__ == '__main__':
    save_plt(12)
    save_plt(1,'minute')